package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import orm.CollectivityTeachingPlanWeek;
import orm.Teacher;
import util.DBHelper;

public class CTeachingPlanWeekDAO {
	
	public void insertCTPWeek(Statement statement, CollectivityTeachingPlanWeek ctpw) throws SQLException {
		statement.execute("insert into collectivityteachingplanweek values('" + ctpw.getCWeekArrangeID() + "','" + ctpw.getClassID() + "','" +
				ctpw.getExecutionDate() + "', '" + ctpw.getExecutionWeek() + "', '" + ctpw.getMonthThemeName() +
				"', '" + ctpw.getWeekThemeName() + "', '" + ctpw.getWeekTarget() + "','" + ctpw.getStartDate() + "' , '" + 
				ctpw.getConstitutor() + "', " + ctpw.getTeacherID() + ")");
	}
	
	public void updateCTPWeek(Statement statement, CollectivityTeachingPlanWeek ctpweek) throws SQLException {
		statement.execute("update collectivityteachingplanweek set ClassID = '" +
				ctpweek.getClassID() + "', ExecutionDate = '" + ctpweek.getExecutionDate() + 
				"', ExecutionWeek = '" + ctpweek.getExecutionWeek() + "', MonthThemeName = '" + 
				ctpweek.getMonthThemeName() + "', WeekThemeName = '" + ctpweek.getWeekThemeName() + 
				"', WeekTarget = '" +  ctpweek.getWeekTarget() + "', StartDate = '" + ctpweek.getStartDate() + "', Constitutor = '" + 
				ctpweek.getConstitutor() + "' where CWeekArrangeID = '" + ctpweek.getCWeekArrangeID() + "'");
	}
	
	public void deleteCTPWeek(Statement statement, String cWeekArrangeID) throws SQLException {
		statement.execute("delete from collectivityteachingplanweek where CWeekArrangeID='" + cWeekArrangeID + "'");
	}
	
	
	public CollectivityTeachingPlanWeek getCTPWeekByCWAID(String cWeekArrangeID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		CollectivityTeachingPlanWeek ctpweek = new CollectivityTeachingPlanWeek();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from collectivityteachingplanweek where CWeekArrangeID='" + cWeekArrangeID + "'");
		
		while (rs.next()) {
			int i = 1;
			ctpweek.setCWeekArrangeID(rs.getString(i++));
			ctpweek.setClassID(rs.getString(i++));
			ctpweek.setExecutionDate(rs.getString(i++));
			ctpweek.setExecutionWeek(rs.getString(i++));
			ctpweek.setMonthThemeName(rs.getString(i++));
			ctpweek.setWeekThemeName(rs.getString(i++));
			ctpweek.setWeekTarget(rs.getString(i++));
			ctpweek.setStartDate(rs.getString(i++));
			ctpweek.setConstitutor(rs.getString(i++));
			ctpweek.setTeacherID(rs.getInt(i++));
			
		}
		
		rs.close();
		stmt.close();
		conn.close();
		
		return ctpweek;
	}
	
	public ArrayList<CollectivityTeachingPlanWeek> getAllCTeachingPlanWeek(int teacherID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		ArrayList<CollectivityTeachingPlanWeek> al = new ArrayList<CollectivityTeachingPlanWeek>();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();

		rs = stmt.executeQuery("select CWeekArrangeID,MonthThemeName,WeekThemeName, Constitutor, ClassID, ExecutionDate from collectivityteachingplanweek where TeacherID = " + teacherID + " ORDER BY CWeekArrangeID ASC");
		CollectivityTeachingPlanWeek CTPWeek = null;
		
		while (rs.next()) {
			CTPWeek = new CollectivityTeachingPlanWeek();
			CTPWeek.setCWeekArrangeID(rs.getString("CWeekArrangeID"));
			CTPWeek.setMonthThemeName(rs.getString("MonthThemeName"));
			CTPWeek.setWeekThemeName(rs.getString("WeekThemeName"));
			CTPWeek.setConstitutor(rs.getString("Constitutor"));
			CTPWeek.setClassID(rs.getString("ClassID"));
			CTPWeek.setExecutionDate(rs.getString("ExecutionDate"));
			
			al.add(CTPWeek);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
	public CollectivityTeachingPlanWeek getCTPWNetByCWAID(String cWeekArrangeID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		CollectivityTeachingPlanWeek ctpweek = new CollectivityTeachingPlanWeek();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select ExecutionWeek, WeekThemeName from collectivityteachingplanweek where CWeekArrangeID='" + cWeekArrangeID + "'");
		
		while (rs.next()) {
			int i = 1;
			ctpweek.setExecutionWeek(rs.getString(i++));
			ctpweek.setWeekThemeName(rs.getString(i++));
		}
		
		rs.close();
		stmt.close();
		conn.close();
		
		return ctpweek;
	}
	
	public ArrayList<CollectivityTeachingPlanWeek> getSearchCondition(String monthThemeName,String weekThemeName,String executionDate, int teacherID) 
			throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		ArrayList<CollectivityTeachingPlanWeek> al = new ArrayList<CollectivityTeachingPlanWeek>();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select CWeekArrangeID,MonthThemeName,WeekThemeName, Constitutor, ClassID, ExecutionDate ");
		sqlStr.append("from collectivityteachingplanweek ");
		sqlStr.append("where TeacherID=" + teacherID + " and CWeekArrangeID != '1' ");
		
		if(monthThemeName!=null && !monthThemeName.equals("")){
			sqlStr.append(" and MonthThemeName like '%" + monthThemeName + "%'");
		}
		if(weekThemeName!=null && !weekThemeName.equals("")){
			sqlStr.append(" and WeekThemeName like '%" + weekThemeName + "%'");
		}
		if(executionDate!=null && !executionDate.equals("")){
			sqlStr.append(" and ExecutionDate = '" + executionDate + "'");
		}
		sqlStr.append(" ORDER BY CWeekArrangeID ASC ");
		
		rs = stmt.executeQuery(sqlStr.toString());
		
		CollectivityTeachingPlanWeek CTPWeek = null;
		
		while (rs.next()) {
			CTPWeek = new CollectivityTeachingPlanWeek();
			CTPWeek.setCWeekArrangeID(rs.getString("CWeekArrangeID"));
			CTPWeek.setMonthThemeName(rs.getString("MonthThemeName"));
			CTPWeek.setWeekThemeName(rs.getString("WeekThemeName"));
			CTPWeek.setConstitutor(rs.getString("Constitutor"));
			CTPWeek.setClassID(rs.getString("ClassID"));
			CTPWeek.setExecutionDate(rs.getString("ExecutionDate"));
			
			al.add(CTPWeek);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
	
}
